package db;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;


/**
 * Here, all DB2 querys are made.
 * 
 * @author antonia
 *
 */

public class DB2Query {
	
    Connection con=null;
    Statement stmt=null;
    PreparedStatement pstmt=null;
    ResultSet rs=null;
    String query, url, userid, passwd;
    static boolean debug = false;
    private ArrayList<Integer> brokenC=null; //all indexes of countries not in db
    private ArrayList<Integer> brokenP=null;
    ArrayList<Organization> orgas=null;
    ArrayList<Place> places=null;
    ArrayList<Country> countries;
    ArrayList<ArrayList<String>> batches=null;
    
    /**
     * Constuctor.
     */
	public DB2Query(Controller con){
		
		try {
	        Class.forName("com.ibm.db2.jcc.DB2Driver"); // Typ-4-Treiber

	      } catch (Exception e) {
	        e.printStackTrace();
	        System.err.println("JDBC-Treiber konnte nicht geladen werden.\n" +
	                           "Bitte den CLASSPATH ueberpruefen.");
	        System.exit(1);
	      }

	    url = "jdbc:db2://leutzsch.informatik.uni-leipzig.de:50001/dbprak11";
	    
		userid = "dbprak13";
		passwd = ".prA$K13";
		brokenC = new ArrayList<Integer>();
		brokenP = new ArrayList<Integer>();
		orgas= con.getOrganizations();
		places=con.getPlaces();
		countries=con.getCountries();
		batches = new ArrayList<ArrayList<String>>();
		
	}
	
	/**
	 * Shorthand for filling complete data in DB.
	 */
	public void fillDB(){
		makeEmpty("all");
		fillOrga();
		fillCountry();
		fillPlaces();
		fillMember();
		fillYeardata();
		fillArgriculture();
		fillReligions();
		fillBorders();
		fillPlacesInCountries();
	}
	
	/**
	 * Clean all tables.
	 */
	public void makeEmpty() {makeEmpty("all");}
	
	/**
	 * Clean all derived tables from <what>.
	 * @param what "orga", "places", "country" or "all".
	 */
	public void makeEmpty(String what) {
		System.out.println("Cleaning DB2 tables: "+what+" ...");
		try {
			// Verbindung herstellen
			con = DriverManager.getConnection(url, userid, passwd);

			stmt = con.createStatement();
			
			if(what == "orga"||what =="all"){ //lösche/erstelle organizations
				query = "Delete From Member";
				stmt.executeUpdate(query);
				
				query = "Delete From Organization";
				stmt.executeUpdate(query);
			}
			if (what == "places"||what =="all"){  //lösche+erstelle places und places_in_country
				query = "Delete From Places_in_Country";
				stmt.executeUpdate(query);
				
				query = "Delete From Place";
				stmt.executeUpdate(query);
			}
			if(what == "country"||what =="all"){ //lösche alle inhalte die mit country verknüpft sind
				if(what != "all"){
					query = "Delete From Places_in_Country";
					stmt.executeUpdate(query);
				}
				
				query = "Delete From Agriculture";
				stmt.executeUpdate(query);
				
				query = "Delete From Yeardata";
				stmt.executeUpdate(query);
				
				query = "Delete From Religion";
				stmt.executeUpdate(query);
				
				query = "Delete From Border";
				stmt.executeUpdate(query);
				
				query = "Delete From Country";
				stmt.executeUpdate(query);
			}

		} catch (SQLException e) {
			System.err
					.println("Fehler beim Ausfuehren einer Datenbankoperation aufgetreten:\n"
							+ e.getMessage());
			e.printStackTrace();
			LogDat.getInstance().addLog("Error cleaning Tables. "+ e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e1) {
				System.err.println(e1.getMessage());
			}
			;
		}
	}
	/**
	 * Gets the organizations data from the objects in the DB2-Tables.
	 */
	public void fillOrga() {
		System.out.println("filling organizations ... ");
		batches.clear();
		try {
			con = DriverManager.getConnection(url, userid, passwd);

			pstmt = con.prepareStatement("INSERT INTO Organization (Name, Abbreviation) " + 
					"VALUES ( ?, ?)");
			
			for( Organization o : orgas){
				//if(debug) System.out.println("name: " + o.getName());
				
				ArrayList<String> names = o.getAltName();
				
				ArrayList<String> batch = new ArrayList<String>();
				
				if(names.size() > 1){
					String altnames = "";
					for(String n : names){
						altnames += n;
						if (names.indexOf(n) != names.size()-1) altnames += ", ";
					}
					setPstmtString(1, altnames);
					batch.add(altnames);
					//if(debug) System.out.println("AltNames: " + altnames);
				}else if(names.size() == 1){
					setPstmtString(1, names.get(0));
					batch.add(names.get(0));
					//if(debug) System.out.println("AltNames: " + names.get(0));
				}else {
					pstmt.setNull(1, java.sql.Types.VARCHAR);
					batch.add("null");
				}
				
				setPstmtString(2, o.getName());
				batch.add(o.getName());
				batches.add(batch);
				pstmt.addBatch();
			}
			pstmt.executeBatch();

		} catch (SQLException e) {
			//e.printStackTrace();
			e.getNextException();
			while (e != null) {  
				String error = getError(e);
		        //System.out.println(error);  
		        LogDat.getInstance().addLog("In DB2Query.fillOrga(): " + error);
		        e = e.getNextException();  
		        //System.out.println("");
		    } 
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					pstmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e1) {
				System.err.println(e1.getMessage());
			}
			;
		}
	}
	
	/**
	 * Gets the places data from the objects in the DB2-Tables.
	 */
	public void fillPlaces() {
		System.out.println("filling places ...");
		batches.clear();
		int it=0;
		try {

			con = DriverManager.getConnection(url, userid, passwd);

			pstmt = con.prepareStatement("INSERT INTO Place (Latitude, Longitude, Port, " +
					"Name, Id_place, Inhabitants, Full_name, isCapital) " + 
					"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?)");
			
			for( Place p : places){
				//if(debug) System.out.println("insert name: " + p.getName());
				
				if(brokenC.contains(countries.indexOf(p.getCountry()))){
					brokenP.add(it);
					it++;
					continue;
				}
				
				ArrayList<String> batch = new ArrayList<String>();
				
				setPstmtDouble(1, p.getLatitude());
				batch.add(String.valueOf(p.getLatitude()));
				
				setPstmtDouble(2, p.getLongitude());
				batch.add(String.valueOf(p.getLongitude()));
				
				pstmt.setInt(3, (p.isPort()?1:0));
				batch.add(String.valueOf((p.isPort()?1:0)));
				
				setPstmtString(4, p.getName());
				batch.add(p.getName());
				
				pstmt.setInt(5, it);
				batch.add(String.valueOf(it));
				
				setPstmtInt(6, p.getInhabitants());
				batch.add(String.valueOf(p.getInhabitants()));
				
				setPstmtString(7, p.getFullName());
				batch.add(((p.getFullName()== null)?"null":p.getFullName()));
				
				pstmt.setInt(8, 0);
				batch.add(String.valueOf("0"));
				
				for(Country c : countries){
					if(c.getCapital() == p) {
						pstmt.setInt(8, 1);
						batch.set(7, "1");
						break;
					}
				}
				pstmt.addBatch();
				batches.add(batch);

				it++;
			}
			int[] count = pstmt.executeBatch();
			
			if(debug)for(int i=0;i<count.length;i++){
				System.out.println("Result "+i+": "+ count[i]);
			}
		} catch (SQLException e) {
			e.getNextException();
//			e.printStackTrace();
			while (e != null) {  
				String error = getError(e, 1);
		        //System.out.println(error);  
		        LogDat.getInstance().addLog("In DB2Query.fillPlaces(): " + error);
		        e = e.getNextException();  
		        //System.out.println("");
		    }  
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					pstmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e1) {
				System.err.println(e1.getMessage());
			}
			;
		}
	}
	
	/**
	 * Fills the country table.
	 */
	public void fillCountry() {
		System.out.println("filling countries ...");
		batches.clear();
		int it = 0;
		
//		for( Country c : countries){
//			System.out.print(c.getShortName() + ", ");
//		}
		try { 
			con = DriverManager.getConnection(url, userid, passwd);

			pstmt = con.prepareStatement("INSERT INTO COUNTRY (AREA, NAME, INFO, SHORT, Id_country,"+
					"CATASTROPHE, LOCATION, FLAG_LINK, MAP_LINK, CONTINENT) " + 
					"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
			
			for( Country c : countries){
				if(debug) System.out.println(c.getShortName());
				
				//test if the country still exists (has last yeardata)
				boolean actualCountry = false;
				for(Year y : c.getYears()){
					if(y.getYear()==2003) actualCountry=true;
				}
				if(c.getCapital() == null || !actualCountry) {
					brokenC.add(it);
					it++;
					continue;
				}
				
				ArrayList<String> batch = new ArrayList<String>();
				
				setPstmtDouble(1, c.getArea());
				batch.add(String.valueOf(c.getArea()));
				
				setPstmtString(2, c.getName());
				batch.add(c.getName());
				
				setPstmtString(3, c.getInfo());
				batch.add(c.getInfo());
				
				setPstmtString(4, c.getShortName());	
				batch.add(c.getShortName());
				
				pstmt.setInt(5, it);
				batch.add(String.valueOf(it));
				
				setPstmtString(6, c.getHazards());
				batch.add(c.getHazards());
				
				setPstmtString(7, c.getLocation());
				batch.add(c.getLocation());
				
				setPstmtString(8, c.getFlagLink());
				batch.add(c.getFlagLink());
				
				setPstmtString(9, c.getMapLink());
				batch.add(c.getMapLink());
				
				setPstmtString(10, c.getContinent());
				batch.add(c.getContinent());
				
				pstmt.addBatch();
				batches.add(batch);
				it++;
			}
			pstmt.executeBatch();
			
		} catch (SQLException e) {

			e = e.getNextException();
			while (e != null) {  
				String error = getError(e, 2);
		        //System.out.println(error);  
		        LogDat.getInstance().addLog("In DB2Query.fillCountries(): " + error);
		        e = e.getNextException();  
		        //System.out.println("");
		    }  
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					pstmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e1) {
				System.err.println(e1.getMessage());
			}
			;
		}
	}
	
	/**
	 * Fills the membership mapping table of orgas and countries.
	 */
	public void fillMember() {
		System.out.println("filling memberships ...");
		batches.clear();
		try {
			con = DriverManager.getConnection(url, userid, passwd);

			pstmt = con.prepareStatement("INSERT INTO Member (Id_country, Abbreviation) " + 
					"VALUES ( ?, ?)");
			for( Country c : countries){
				if( brokenC.contains(countries.indexOf(c)))continue; //wenn c nicht in db, überspringen
				if(debug) System.out.println("Inserting memberships from "+c.getShortName()
						+ " ID: "+ countries.indexOf(c));
				// insert orgas..
				pstmt.setInt(1, countries.indexOf(c));
				for (String o : c.getOrgs()) {
					pstmt.setString(2, o);
					pstmt.addBatch();
					ArrayList<String> batch = new ArrayList<String>();
					batch.add(String.valueOf(countries.indexOf(c)));
					batch.add(o);
					batches.add(batch);
				}
			}
			System.out.println("still filling memberships ...");
			pstmt.executeBatch(); //this could take a wile ;)

		} catch (SQLException e) {

			e = e.getNextException();
			while (e != null) { 
				if (!(e.getErrorCode() == -803)) { // not all orgas may be in
													// the db, so thats OK
					String error = getError(e);
					//System.out.println(error);
					LogDat.getInstance().addLog("In DB2Query.fillMember(): " + error);
					//System.out.println("");
				}
				e = e.getNextException();
		    }  
		} finally {
			try {
				if (rs != null)rs.close();
				if (stmt != null)pstmt.close();
				if (con != null)con.close();
			} catch (SQLException e1) {
				System.err.println(e1.getMessage());
			}
			;
		}
	}
	
	/**
	 * Fills the yeardata table.
	 */
	public void fillYeardata() {
		System.out.println("filling yeardata ...");
		batches.clear();
		try {
			con = DriverManager.getConnection(url, userid, passwd);

			pstmt = con.prepareStatement("INSERT INTO Yeardata (Year, Gdp, Sex_ratio_birth, "+
					"Life_expectancy_m, Life_expectancy_w, Sex_ratio_total, Population, Id_country) " + 
					"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?)");
			
			for( Country c : countries){
				if( brokenC.contains(countries.indexOf(c)))continue;
				
				if(debug) System.out.println("Inserting yeardata from "+c.getShortName());
				
				// insert Yeardata..
				pstmt.setInt(8, countries.indexOf(c));
				for (Year y : c.getYears()) { // für alle Jahresdaten von Land
												// x..
					int year = y.getYear();
					if (debug)
						System.out.println("Year: " + year);
					
					pstmt.setInt(1, year);
					setPstmtInt(2, (int) y.getGdp());
					setPstmtDouble(3, y.getSexRatioBirth());
					setPstmtDouble(4, y.getLifeExpMale());
					setPstmtDouble(5, y.getLifeExpFemale());
					setPstmtDouble(6, y.getSexRatioTotal());
					setPstmtInt(7, y.getPopulation());
					pstmt.addBatch();
					
					ArrayList<String> batch = new ArrayList<String>();
					batch.add(String.valueOf(y.getGdp()));
					batch.add(String.valueOf(y.getSexRatioBirth()));
					batch.add(String.valueOf(y.getLifeExpMale()));
					batch.add(String.valueOf(y.getLifeExpFemale()));
					batch.add(String.valueOf(y.getSexRatioTotal()));
					batch.add(String.valueOf(y.getPopulation()));
					batch.add(String.valueOf(countries.indexOf(c)));
					batches.add(batch);
				}
			}
			System.out.println("Filling yeardata table");
			pstmt.executeBatch();

		} catch (SQLException e) {

			e = e.getNextException();
			while (e != null) {
				if (!(e.getErrorCode() == -803)) { // not all countries are in
													// the db, so thats OK
					String error = getError(e);
					//System.out.println(error);
					LogDat.getInstance().addLog("In DB2Query.fillYeardata(): " + error);
					//System.out.println("");
				}
				e = e.getNextException();
			}
		} finally {
			try {
				if (rs != null)rs.close();
				if (stmt != null)pstmt.close();
				if (con != null)con.close();
			} catch (SQLException e1) {
				System.err.println(e1.getMessage());
			}
			;
		}
	}
	
	/**
	 * Fills the argriculture table.
	 */
	public void fillArgriculture() {
		System.out.println("filling argriculture ...");
		batches.clear();
		try {
			con = DriverManager.getConnection(url, userid, passwd);

			pstmt = con.prepareStatement("insert into Agriculture (Product,Year,Id_country) "+
					"values(?,?,?)");
			
			for( Country c : countries){
				if( brokenC.contains(countries.indexOf(c)))continue;
				
				if(debug) System.out.println("Inserting yeardata from "+c.getShortName()
						+ " : " + countries.indexOf(c) );
				
				// insert products..
				pstmt.setInt(3, countries.indexOf(c));
				for (Year y : c.getYears()) { // für alle Jahresdaten von Land
												// x..
					int year = y.getYear();
					for(String pr:y.getProducts()){
						if(debug)System.out.println("Product: "+ pr);
						setPstmtString(1, pr);
						setPstmtInt(2, year);
						pstmt.addBatch();
						
						ArrayList<String> batch = new ArrayList<String>();
						batch.add(pr);
						batch.add(String.valueOf(year));
						batch.add(String.valueOf(countries.indexOf(c)));
						batches.add(batch);
					}
				}
			}
			pstmt.executeBatch();

		} catch (SQLException e) {

			e = e.getNextException();
			while (e != null) {
				if (!(e.getErrorCode() == -803)) { // not all countries are in
													// the db, so thats OK
					String error = getError(e);
					//System.out.println(error);
					LogDat.getInstance().addLog("In DB2Query.fillArgriculture(): " + error);
					//System.out.println("");
				}
				e = e.getNextException();
			}
		} finally {
			try {
				if (rs != null)rs.close();
				if (stmt != null)pstmt.close();
				if (con != null)con.close();
			} catch (SQLException e1) {
				System.err.println(e1.getMessage());
			}
			;
		}
	}

	/**
	 * Fills the religions table.
	 */
	public void fillReligions() {
		System.out.println("filling religions ...");
		batches.clear();
		try {
			con = DriverManager.getConnection(url, userid, passwd);

			pstmt = con.prepareStatement("insert into Religion (Percent,R_name,Year,Id_country) "+
					"values(?,?,?,?)");
			
			for( Country c : countries){
				if( brokenC.contains(countries.indexOf(c)))continue;
				
				if(debug) System.out.println("Inserting religions from "+c.getShortName());
				
				// insert religions..
				pstmt.setInt(4, countries.indexOf(c));
				for (Year y : c.getYears()) { // für alle Jahresdaten von Land
					// x..
					pstmt.setInt(3, y.getYear());
					for (Religion r : y.getReligions()) {
						pstmt.setDouble(1, r.getPercent());
						setPstmtString(2, r.getName());
						pstmt.addBatch();
						
						ArrayList<String> batch = new ArrayList<String>();
						batch.add(String.valueOf(r.getPercent()));
						batch.add(r.getName());
						batch.add(String.valueOf(y.getYear()));
						batch.add(String.valueOf(countries.indexOf(c)));
						batches.add(batch);
					}
				}
			}
			pstmt.executeBatch();

		} catch (SQLException e) {
			e = e.getNextException();
			while (e != null) {
				if (!(e.getErrorCode() == -803)) { // not all countries are in
													// the db, so thats OK
					String error = getError(e);
					//System.out.println(error);
					LogDat.getInstance().addLog("In DB2Query.fillReligions(): " + error);
					//System.out.println("");
				}
				e = e.getNextException();
			}
		} finally {
			try {
				if (rs != null)rs.close();
				if (stmt != null)pstmt.close();
				if (con != null)con.close();
			} catch (SQLException e1) {
				System.err.println(e1.getMessage());
			}
			;
		}
	}
	
	/**
	 * Fills the borders table.
	 */
	public void fillBorders() {
		System.out.println("filling borders ...");
		batches.clear();
		try {
			con = DriverManager.getConnection(url, userid, passwd);

			pstmt = con.prepareStatement("insert into Border (Length,Id_country,Id_country_neig) "+
					"values(?,?,?)");
			
			for( Country c : countries){
				if( brokenC.contains(countries.indexOf(c)))continue;
				
				// insert borders..
				for (Border b : c.getBorders()) {
					//get Neighbour
					int neigh = 0;
					for(Country n : countries){
						if(n.getShortName().equals(b.getNeighbor())){
							if(debug)System.out.println(n.getShortName()+": "+ countries.indexOf(c)
									+ " equals " + b.getNeighbor()+": "+ neigh);
							break;
						}else neigh++;
					}
					if(neigh > 0 
					  && countries.indexOf(c) < neigh
					  && !brokenC.contains(neigh)){
					if(debug)System.out.println("------->>>>"+c.getShortName()+": "+ countries.indexOf(c)
							+ " and " + b.getNeighbor()+": "+ neigh);
					setPstmtDouble(1, b.getLength());
					pstmt.setInt(2, countries.indexOf(c));
					pstmt.setInt(3, neigh);
					pstmt.addBatch();
					
					ArrayList<String> batch = new ArrayList<String>();
					batch.add(String.valueOf(b.getLength()));
					batch.add(String.valueOf(countries.indexOf(c)));
					batch.add(String.valueOf(neigh));
					batches.add(batch);}
				}
			}
			pstmt.executeBatch();

		} catch (SQLException e) {
			e = e.getNextException();
			while (e != null) {
				if (!(e.getErrorCode() == -803)) { // not all countries are in
													// the db, so thats OK
					String error = getError(e);
					//System.out.println(error);
					LogDat.getInstance().addLog("In DB2Query.fillBorders(): " + error);
					//System.out.println("");
				}
				e = e.getNextException();
			}
		} finally {
			try {
				if (rs != null)rs.close();
				if (stmt != null)pstmt.close();
				if (con != null)con.close();
			} catch (SQLException e1) {
				System.err.println(e1.getMessage());
			}
			;
		}
	}
	
	/**
	 * Fills the places_in_country table.
	 */
	public void fillPlacesInCountries() {
		System.out.println("filling Places_in_Country ...");
		try {
			con = DriverManager.getConnection(url, userid, passwd);

			pstmt = con.prepareStatement("insert into Places_in_Country (Id_place,Id_country) "+
					"values(?,?)");
			
			for( Place p : places){
				if( brokenP.contains(places.indexOf(p)))continue;
				pstmt.setInt(1, places.indexOf(p));
				for(Country c : countries){
					if(brokenC.contains(c))continue;
					if(p.getCountry()==c){
						pstmt.setInt(2, countries.indexOf(c));
						pstmt.addBatch();
						
						ArrayList<String> batch = new ArrayList<String>();
						batch.add(String.valueOf(places.indexOf(p)));
						batch.add(String.valueOf(countries.indexOf(c)));
						batches.add(batch);
					}
				}
			}
			pstmt.executeBatch();

		} catch (SQLException e) {
			e = e.getNextException();
			while (e != null) {
				if (!(e.getErrorCode() == -803)) { // not all countries are in
													// the db, so thats OK
					String error = getError(e);
					//System.out.println(error);
					LogDat.getInstance().addLog("In DB2Query.fillPlacesInCountries(): " + error);
					//System.out.println("");
				}
				e = e.getNextException();
			}
		} finally {
			try {
				if (rs != null)rs.close();
				if (stmt != null)pstmt.close();
				if (con != null)con.close();
			} catch (SQLException e1) {
				System.err.println(e1.getMessage());
			}
			;
		}
	}
	
	/**
	 * Catches all zero values an sets them null in query.
	 * @param num argument number
	 * @param val string argument
	 * @throws SQLException
	 */
	private void setPstmtString( int num, String val) throws SQLException{
		if( val == null){ pstmt.setNull(num, java.sql.Types.VARCHAR);
		}else pstmt.setString(num, val);
	}
	
	/**
	 * Catches all zero values an sets them null in query.
	 * @param num argument number
	 * @param val string argument
	 * @throws SQLException
	 */
//	private void setPstmtClob( int num, String val) throws SQLException{
//		if( val == null){ pstmt.setNull(num, java.sql.Types.VARCHAR);
//		}else{
//			//StringReader in = new StringReader(val);
//			//BufferedReader reader = new BufferedReader(in);
//			Clob clob = 
//			pstmt.setClob(num, in, val.length());
//			
//			//pstmt.setClob(num, (Reader) in);
//		}
//	}
	
	/**
	 * Catches all zero values an sets them null in query.
	 * @param num argument number
	 * @param val double argument
	 * @throws SQLException
	 */
	private void setPstmtDouble( int num, Double val) throws SQLException{
		if( val == null){ pstmt.setNull(num, java.sql.Types.DOUBLE);
		}else if (val == 0.) pstmt.setNull(num, java.sql.Types.DOUBLE);
		else pstmt.setDouble(num, val);
	}
	
	/**
	 * Catches all zero values an sets them null in query.
	 * @param num argument number
	 * @param val integer argument
	 * @throws SQLException
	 */
	private void setPstmtInt( int num, Integer val) throws SQLException{
		if( val == null){ pstmt.setNull(num, java.sql.Types.INTEGER);
		}else if( val == 0) pstmt.setNull(num, java.sql.Types.INTEGER);
		else pstmt.setInt(num, val);
	}
	
	/**
	 * Gets error cause and affected batchelement from error message and batches list.
	 * @param e trowed Exception
	 * @return String with Explanations.
	 */
	private String getError(SQLException e){
		return getError(e, 0);
	}
	
	/**
	 * Gets error cause and affected batchelement from error message and batches list.
	 * @param e trowed Exception
	 * @param fillbroken 1-brokenP, 2brokenC, fills the error indices in the list.
	 * @return String with Explanations.
	 */
	private String getError(SQLException e, int fillbroken){
		
		String error = "SQL Error: ";

		//parse  element number
		String message = e.getMessage();
		int startNr = message.indexOf("element #")+9;
		if(startNr < 0) return error;
		int endNr = message.substring(startNr).indexOf(": ");
		if(endNr < 0) return error;
		String test = message.substring(startNr,startNr+endNr);

		int elementNum = Integer.parseInt(test);
		
		int sqlErr = e.getErrorCode();
		
		//parse error location
		startNr = message.indexOf("SQLERRMC: ");
		String loc = message.substring(startNr);
		if(debug)System.out.println("loc: " + loc);
		if(debug)System.out.println("sqlstate: " + e.getSQLState());
		
		error += sqlErr;
		if(sqlErr == -302){
			error += ": too large for table ";
		}else if(sqlErr == -407){
			error += ": forbidden null values ";
		}else if(sqlErr == -530){
			error += ": invalid foreign key ";
		}else error += ": unexpected ";
		
		if(loc.contains("COLNO")) error += ":: " + loc;
		
		error += "\nElement number: " + elementNum;
		int it = 1;
		for( String el : batches.get(elementNum-1)){
			error += "\n#"+ it + ": " + el;
			it++;
		}
		
		if(fillbroken==1){
			brokenP.add(elementNum-1);
		}else if(fillbroken==2){
			brokenC.add(elementNum-1);
		}
		
		return error;
	}
}
